﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using DataAccessLayer;
using BusinessLogicLayer;
using BusinessLogicLayer.MetaDataList;

using Global.Class.Library;

namespace PresentationLayer
{
    public static class CommonStaticMethod
    {
        public static void pfCustomerDeleteOrphan()
        {
            try
            {
                DataTable dataTable = SQLDatabase.GetDataTable("SELECT CustomerID FROM ListCustomerName WHERE DescriptionOriginal NOT IN (SELECT ColumnValue FROM ListCustomerNameColumn) AND DependentOnly = 1");
                foreach (DataRow dataRow in dataTable.Rows)
                {
                    ListCustomerNameBLL listCustomerNameBLL = new ListCustomerNameBLL();
                    listCustomerNameBLL.Fill((int) dataRow["CustomerID"]);
                    listCustomerNameBLL.Delete();
                }
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }


        public static void lSaveCustomerCodeMap(int customerID)
        {
            try
            {
                int countZuellig = 0; int countDiethelm = 0;
                string SQL = ""; string SQV = ""; int countCustomerMapping = 0;

                DataTable lRec = SQLDatabase.GetDataTable("SELECT * FROM ListDistributorName WHERE DistributorID >= 89");   //NEW DISTRIBUTOR => ADD NEW COLUMN IN ListCustomerNameColumnFull (DIETHELM & ZUELLIG: FIX CODE, KHONG AP DUNG CACH NAY)
                foreach (DataRow lRecRow in lRec.Rows)
                {
                    SQL = SQL + ", " + "Description" + lRecRow["DistributorID"] + ", " + "Count" + lRecRow["DistributorID"];
                    SQV = SQV + ", N'" + CommonStaticMethod.GetExternalCode((int)lRecRow["DistributorID"], customerID, out countCustomerMapping) + "', " + countCustomerMapping;
                }

                SQLDatabase.ExecuteNonQuery("DELETE FROM ListCustomerNameColumnFull WHERE CustomerID = " + customerID);
                SQLDatabase.ExecuteNonQuery("INSERT INTO ListCustomerNameColumnFull (CustomerID, DescriptionZuellig, DescriptionDiethelm, CountZuellig, CountDiethelm" + SQL + ") VALUES(" + customerID + ", N'" + CommonStaticMethod.GetExternalCode(88, customerID, out countZuellig) + "', N'" + CommonStaticMethod.GetExternalCode(82, customerID, out countDiethelm) + "', " + countZuellig + ", " + countDiethelm + SQV + ")");
            }
            catch (Exception exception)
            {
                throw exception;
            }

        }


        public static void lSaveCustomerCodeUnMap(string columnValue)
        {
            try
            {
                DataTable dataTable = SQLDatabase.GetDataTable("SELECT TOP 1 CustomerID FROM ListCustomerNameColumnFull WHERE " + (GlobalVariables.DistributorID == 88 ? "DescriptionZuellig" : (GlobalVariables.DistributorID == 82 ? "DescriptionDiethelm" : "Description" + GlobalVariables.DistributorID)) + " LIKE '%" + columnValue + "%'");
                if (dataTable.Rows.Count > 0) lSaveCustomerCodeMap((int)dataTable.Rows[0]["CustomerID"]);
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }


        private static  string GetExternalCode(int distributorID, int customerID, out int countCustomerMapping)
        {
            try
            {
                string externalCode = "";
                DataTable lRec = SQLDatabase.GetDataTable("SELECT ColumnValue FROM ListCustomerNameColumn WHERE DistributorID = " + distributorID + " AND CustomerID = " + customerID + " ORDER BY ColumnValue ");
                foreach (DataRow lRecRow in lRec.Rows)
                    externalCode = externalCode + (externalCode != "" ? "; " : "") + lRecRow["ColumnValue"];

                countCustomerMapping = lRec.Rows.Count;

                return externalCode;
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }



    }
}
